/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sgbl.dao;

import br.com.sgbl.bean.Cardapio;
import br.com.sgbl.utils.Conexao;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author heidi
 */
public class CardapioDao {

    private static final String SQL_LISTAR =
            "SELECT * FROM cardapio order by id";
    private static final String SQL_INSERIR =
            "INSERT INTO cardapio (id, datainicio, datafim, turno) VALUES (?,?,?,?)";
    private static final String SQL_ALTERAR =
            "UPDATE cardapio SET datainicio = ?, datafim = ?,turno = ? WHERE id = ?";
    private static final String SQL_EXCLUIR =
            "DELETE FROM cardapio WHERE id = ?";

    public Cardapio localizar(Connection conn, long codigo) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(SQL_LISTAR + " where id= ?");
        ps.setLong(1, codigo);

        ResultSet rs = ps.executeQuery();

        if (rs.first()) {
            Cardapio cardapio = new Cardapio();
            cardapio.setId(rs.getLong("id"));
            cardapio.setDataInicio(rs.getDate("datainicio"));
            cardapio.setDataFim(rs.getDate("datafim"));
            cardapio.setTurno(rs.getString("turno"));

            return cardapio;
        } else {
            return null;
        }
    }

    public Cardapio salvar(Cardapio cardapio) throws SQLException {
        if (cardapio.getId() == 0) {
            return inserir(cardapio);
        } else {
            return alterar(cardapio);
        }
    }

    private Cardapio inserir(Cardapio cardapio) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_INSERIR,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            ps.setNull(1, Types.NULL);
            ps.setDate(2, cardapio.getDataInicio());
            ps.setDate(3, cardapio.getDataFim());
            ps.setString(4, cardapio.getTurno());

            ps.executeUpdate();

            ResultSet rs = ps.getGeneratedKeys();
            rs.first();

            cardapio.setId(rs.getLong("GENERATED_KEY"));

            conn.commit();
        } finally {
            conn.close();
        }

        return cardapio;
    }

    private Cardapio alterar(Cardapio cardapio) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_ALTERAR);

            ps.setDate(1, (java.sql.Date) (Date) cardapio.getDataInicio());
            ps.setDate(2, (java.sql.Date) (Date) cardapio.getDataFim());
            ps.setString(3, cardapio.getTurno());
            ps.setLong(4, cardapio.getId());


            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }

        return cardapio;
    }

    public List listar() throws SQLException {
        List lista = new ArrayList();

        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_LISTAR);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Cardapio cardapio = new Cardapio();

                cardapio.setId(rs.getLong("id"));
                cardapio.setDataInicio(rs.getDate("datainicio"));
                cardapio.setDataFim(rs.getDate("datafim"));
                cardapio.setTurno(rs.getString("turno"));


                lista.add(cardapio);
            }
        } finally {
            conn.close();
        }

        return lista;
    }

    public void excluir(Cardapio cardapio) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_EXCLUIR);

            ps.setLong(1, cardapio.getId());

            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }
    }
}
